/**
 * 项目名：phms3
 * 日期：    2019-11-22 16:35:44
 * Copyright (c) 2015- HTSC-版权所有
 */

package com.joysuch.wwyt.core.repository;

import com.joysuch.wwyt.core.entity.BaseUser;
import com.joysuch.wwyt.edu.bean.EduExamAbsentDto;
import com.joysuch.wwyt.edu.bean.EduMarkingPapersCenterListBean;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * BaseBizCodeSeedDao
 */
public interface BaseUserDao extends JpaRepository<BaseUser, Long>, QuerydslPredicateExecutor<BaseUser>, JpaSpecificationExecutor<BaseUser> {
    public Page<BaseUser> findAll(Specification<BaseUser> spec, Pageable pageable);

    public BaseUser save(BaseUser bean);

    public void delete(BaseUser bean);

    @Query(value = "select real_name from wwyt_base_user where id = ?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    // @Query(value = " SELECT REAL_NAME FROM wwyt_base_user  WHERE ID =?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) limit 0,1", nativeQuery = true)
    public String getBaseUserNameById(Long id);

    @Query("select realName from BaseUser where id = ?1")
    // @Query(value = " SELECT REAL_NAME FROM wwyt_base_user  WHERE ID =?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) limit 0,1", nativeQuery = true)
    public String getRealNameById(Long id);

    @Query("select realName from BaseUser where id in :ids")
    List<String> getRealNameByIds(List<Long> ids);

    @Query("select code from BaseUser where id in :ids")
    List<String> getCodeByIds(List<Long> ids);

    @Query(value = " SELECT id FROM wwyt_base_user WHERE code in :codes AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<Long> getIdByCodes(List<String> codes);

    @Query(value = " SELECT id FROM wwyt_base_user WHERE REAL_NAME in :names AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<Long> getIdByNames(List<String> names);

    @Query(value = "select * from  wwyt_base_user where REAL_NAME like concat('%',?1,'%') and delete_flag='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<Long> getNameByIds(String name);

    @Query(value = " SELECT * FROM wwyt_base_user WHERE code = ?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    public BaseUser findByCode(String code);

    @Query(value = " SELECT * FROM wwyt_base_user WHERE code = :code AND TENENT_ID = :tenentId AND ORG_CODE like :orgCode% AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    BaseUser findByCodeAndTenentIdAndOrgCode(@Param("code") String code, @Param("tenentId") Long tenentId, @Param("orgCode") String orgCode);


    @Query(value = " SELECT * FROM wwyt_base_user  WHERE REAL_NAME =?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) limit 0,1", nativeQuery = true)
    public BaseUser findFirstByRealName(String name);

    //没有用到的sql
    @Query(value = " select count(1)  " + "from "
            + "(select wbu.id, wbu.`code`, wbu.real_name `name`, wbu.gender, wbu.birthday, wbu.depart_id departId "
            + " ,(select wbd.`name` from wwyt_bp_depart wbd where wbd.id = wbu.depart_id) departName "
            + " , wbu.create_time, 1 `type`  from wwyt_base_user wbu  where wbu.delete_flag = '0' AND (wbu.state IS NULL OR wbu.state !=44)" + "union "
            + "select wbcs.id, wbcs.`code`, wbcs.`name`, wbcs.gender, wbcs.birthday, wbcs.company_id deparId "
            + " ,(select wbc.`name` from wwyt_bp_contractor wbc where wbc.id = wbcs.company_id) departName "
            + " , wbcs.create_time, 2 `type` from wwyt_bp_contractor_staff wbcs where wbcs.delete_flag = '0' ) temp "
            + "where  temp.`name` like %:name%  " + " and if(:departName !='',temp.departName = :departName,1=1) "
            + " and if(:type !=0,temp.type = :type,1=1) ", nativeQuery = true)
    Integer getAllUserTotalCount(@Param("name") String name, @Param("departName") String departName,
                                 @Param("type") Integer type);

    //没有用到的sql
    @Query(value = " select * "
            + " from (select wbu.id, wbu.`code`, wbu.real_name `name`, wbu.gender, wbu.birthday, wbu.depart_id departId "
            + "  ,(select wbd.`name` from wwyt_bp_depart wbd where wbd.id = wbu.depart_id) departName "
            + "  , wbu.create_time, 1 `type`  from wwyt_base_user wbu where wbu.delete_flag = '0' AND (wbu.state IS NULL OR wbu.state !=44) " + " union "
            + " select wbcs.id, wbcs.`code`, wbcs.`name`, wbcs.gender, wbcs.birthday, wbcs.company_id deparId "
            + "  ,(select wbc.`name` from wwyt_bp_contractor wbc where wbc.id = wbcs.company_id) departName "
            + "  , wbcs.create_time, 2 `type` from wwyt_bp_contractor_staff wbcs  where wbcs.delete_flag = '0' ) temp "
            + " where  temp.`name` like %:name%  " + " and if(:departName !='',temp.departName = :departName,1=1) "
            + " and if(:type !=0,temp.type = :type,1=1) " + " order by temp.create_time desc "
            + " limit :page, :pageSize ", nativeQuery = true)
    List<Map<String, Object>> getAllUser(@Param("page") Integer page, @Param("pageSize") Integer pageSize,
                                         @Param("name") String name, @Param("departName") String departName, @Param("type") Integer type);

    //没有用到的sql?
    // @Query(value = "select m from BaseUser m left join BaseLoginAccount a on m = a.baseUser where a.id is NULL")
    @Query(value = " SELECT * FROM wwyt_base_user m left join wwyt_base_login_account a on m.REAL_NAME = a.USER_NAME WHERE a.ID is NULL AND m.DELETE_FLAG ='0' AND (m.state IS NULL OR m.state !=44) ", nativeQuery = true)
    List<BaseUser> findNoLoginAccountUsers();

    //没有用到的sql?
    // @Query(value = "select m from BaseUser m left join BaseLoginAccount a on m = a.baseUser where a.id is NULL and m.code not in (:codes)")
    @Query(value = " SELECT * FROM wwyt_base_user m left join wwyt_base_login_account a on m.REAL_NAME = a.USER_NAME WHERE a.ID is NULL AND m.code not in (:codes) AND m.DELETE_FLAG ='0' AND (m.state IS NULL OR m.state !=44) ", nativeQuery = true)
    List<BaseUser> findNoLoginAccountUsers(@Param("codes") String[] excludeUserCodes);

    //@Query(value = "select id from BaseUser where realName like %?1% and deleteFlag ='0'")
    @Query(value = " SELECT ID FROM wwyt_base_user WHERE REAL_NAME like %:name% AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    public List<Long> getUserIdByNameContains(@Param("name") String name);

    // @Query(value = "select count(m) from BaseUser m where code=?1")
    @Query(value = " SELECT count(1) FROM wwyt_base_user WHERE code =?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    Long countOfCode(String code);


    @Query(value = "SELECT count(1) FROM wwyt_base_user WHERE REAL_NAME =?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    Long countOfRealName(String realName);

    @Query(value = "SELECT count(1) FROM wwyt_base_user WHERE REAL_NAME =?1 AND id != ?2 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    Long countOfRealNameAndIdNotEq(String realName, Long id);

    // @Query(value = "select count(m) from BaseUser m where code=?1 and id != ?2")
    @Query(value = " SELECT count(1) FROM wwyt_base_user WHERE code =?1 AND ID !=?2 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    Long countOfCodeExcludeId(String code, Long id);

    @Query(value = " SELECT count(1) FROM wwyt_base_user WHERE JOB =?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    int countByJob(Long job);

    @Query(value = " SELECT count(1) FROM wwyt_base_user WHERE JOB in :job AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    int countByJobIn(List<Long> job);

    @Query(value = " SELECT count(1) FROM wwyt_base_user WHERE DEPART_ID in :departId AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    int countByDepartIdIn(List<Long> departId);

    //@Query("select id from BaseUser where departId in :departIds")
    @Query(value = " SELECT Id FROM wwyt_base_user WHERE DEPART_ID in :departIds AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<Long> findIdByDepartIdIn(List<Long> departIds);

    @Query(value = " SELECT * FROM wwyt_base_user WHERE DEPART_ID in :departIds AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<BaseUser> findIdByDepartIdInV2(List<Long> departIds);

    //@Query("select id from BaseUser where job in :jobs")
    @Query(value = " SELECT Id FROM wwyt_base_user WHERE JOB in :jobs AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<Long> findIdByJobIn(List<Long> jobs);

    @Query(value = " SELECT Id FROM wwyt_base_user WHERE JOB = :job AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<Long> findIdByJob(Long job);


    @Query("select new com.joysuch.wwyt.edu.bean.EduExamAbsentDto(bu.id as userId,bu.realName as userName) " +
            "from BaseUser bu where bu.id in :userIds")
    List<EduExamAbsentDto> findAbsentByUserIdIn(List<Long> userIds);

    // @Query(value = "select m.id from BaseUser m where m.departId=?1")
    @Query(value = " SELECT Id FROM wwyt_base_user WHERE DEPART_ID =? AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<Long> findIdByDepartId(Long departId);

    List<BaseUser> findByRealNameAndEmailAndOrgCodeStartingWith(String realName, String email, String orgCode);

    @Query(value = " select * "
            + " from (select  u.id, u.code, u.real_name as realName,  r.role_id as roleId," +
            " (select d.`name` from wwyt_bp_depart d where d.id = u.depart_id) departName ," +
            " (select j.`name` from wwyt_bp_job j where j.id = u.job) jobName, u.tenent_id, u.org_code, u.delete_flag  from wwyt_base_user u   "
            + " left join  "
            + "  (select user_id, role_id from wwyt_base_role_user_login_role where role_id=:roleId) r  " +
            "      on u.id = r.user_id  where  u.delete_flag = '0' and (u.state IS NULL OR u.state !=44)"
            + "  and if( :departId !='',u.depart_Id = :departId,1=1) "
            + " and if( :nameOrCode is null,1=1,u.real_name like %:nameOrCode% or u.code like %:nameOrCode%) "
            + "  and if( :jobId !='',u.job = :jobId,1=1) "
            + " ) as t   order by  t.roleId desc, t.id "
            + " limit :page, :pageSize ", nativeQuery = true)
    List<Map<String, Object>> findRoleUserList(@Param("page") Integer page, @Param("pageSize") Integer pageSize,
                                               @Param("nameOrCode") String name,
                                               @Param("departId") Long departId,
                                               @Param("jobId") Long jobId,
                                               @Param("roleId") Long roleId);

    @Query(value = "select  u.id from wwyt_base_user u  "
            + " where  u.delete_flag = '0' and (u.state IS NULL OR u.state !=44)"
            + "  and if( :departId !='',u.depart_Id = :departId,1=1) "
            + "  and if( :nameOrCode is null,1=1,u.real_name like %:nameOrCode% or u.code like %:nameOrCode%) "
            + "  and if( :jobId !='',u.job = :jobId,1=1) ", nativeQuery = true)
    List<Long> findNewRoleUserList(@Param("nameOrCode") String name,
                                   @Param("departId") Long departId,
                                   @Param("jobId") Long jobId);


    @Query(value = " select count(1)  "
            + " from (select u.tenent_id, u.org_code, u.delete_flag, r.role_id as roleId  from wwyt_base_user u   "
            + " left join  "
            + "  (select user_id, role_id from wwyt_base_role_user_login_role where role_id=:roleId) r  " +
            "      on u.id = r.user_id  where u.delete_flag = '0' and (u.state IS NULL OR u.state !=44)"
            + "  and if(:departId !='',u.depart_Id = :departId,1=1) "
            + " and if( :nameOrCode is null,1=1,u.real_name like %:nameOrCode% or u.code like %:nameOrCode%) "
            + "  and if(:jobId !='',u.job = :jobId,1=1) "
            + " ) as t  where  (t.roleId=:roleId or t.roleId is null )  "
            , nativeQuery = true)
    Integer findRoleUserListCount(
            @Param("nameOrCode") String name,
            @Param("departId") Long departId,
            @Param("jobId") Long jobId,
            @Param("roleId") Long roleId);

    @Query(value = " select r.user_id   from wwyt_base_role_user_login_role r  where   r.role_id =:roleId  ", nativeQuery = true)
    List<Long> findRoleUserListAll(
            @Param("roleId") Long roleId);

    @Query(value = " SELECT * FROM wwyt_base_user WHERE JOB =?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<BaseUser> findByJob(Long roleId);

    @Query(value = " SELECT * FROM wwyt_base_user WHERE JOB in ?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<BaseUser> findByJobs(Set<Long> jobs);

    @Query("select m.realName from BaseUser m where m.id in (:ids)")
    List<String> findRealNameByIdIn(List<Long> ids);


    @Query(value = " SELECT * FROM wwyt_base_user WHERE REAL_NAME =?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<BaseUser> findByRealName(String name);

    @Query(value = " SELECT * FROM wwyt_base_user WHERE DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<BaseUser> getUserList();

    @Query(value = " SELECT * FROM wwyt_base_user WHERE DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<BaseUser> findAll();

    @Query(value = " SELECT * FROM wwyt_base_user WHERE DELETE_FLAG ='0'", nativeQuery = true)
    List<BaseUser> finAllUser();

    @Query(value = " SELECT * FROM wwyt_base_user WHERE code =?1 AND org_code like ?2% and (state IS NULL OR state !=44) and DELETE_FLAG ='0' limit 1 ", nativeQuery = true)
    BaseUser findByCodeAndCompanyCode(String code, String companyCode);

    @Query(value = " SELECT * FROM wwyt_base_user WHERE code =?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<BaseUser> getByCode(String code);

    @Query(value = "select * from wwyt_base_user where (sn is not null and sn !='' ) and employee_Id is null AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<BaseUser> findBySnNotNullAndEmployeeIdNull();

    @Query(value = "select * from wwyt_base_user where (sn is null or sn ='') and employee_Id is not null AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44)", nativeQuery = true)
    List<BaseUser> findBySnNullAndEmployeeIdNotNull();

    @Query(value = " SELECT * FROM wwyt_base_user WHERE DEPART_ID =?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<BaseUser> findBydepartId(Long departId);

    @Query(value = "select id from wwyt_base_user WHERE real_name like %:name% AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44)", nativeQuery = true)
    List<Long> findByNameLike(String name);

    @Query(value = "select id from wwyt_base_user WHERE real_name like %:name% ", nativeQuery = true)
    List<Long> findByNameLikeIgnoreDeleteStatus(String name);

    @Query(value = " SELECT * FROM wwyt_base_user WHERE card_number =?1 and DELETE_FLAG ='0' AND (state IS NULL OR state !=44)", nativeQuery = true)
    List<BaseUser> findByCardNumber(String cardNumber);

    @Query(value = " SELECT * FROM wwyt_base_user WHERE card_number =?1 and DELETE_FLAG ='0' AND (state IS NULL OR state !=44) limit 1", nativeQuery = true)
    BaseUser findFirstByCardNumber(String cardNumber);

    @Query(value = " SELECT * FROM wwyt_base_user WHERE sn =:sn and DELETE_FLAG ='0' AND (state IS NULL OR state !=44) limit 1", nativeQuery = true)
    BaseUser findFirstBySn(String sn);

    //需要查询已删除的人员
    @Query(value = " SELECT id FROM wwyt_base_user WHERE real_name like %:name% and DELETE_FLAG ='0'", nativeQuery = true)
    List<Long> getIdsByNameLike(@Param("name") String name);

    @Query(value = "SELECT id FROM `wwyt_base_user` WHERE (REAL_NAME LIKE %:nameOrCode%  OR CODE LIKE %:nameOrCode% ) AND  DELETE_FLAG ='0' ", nativeQuery = true)
    List<Long> findIdsByRealNameContainingOrCodeContaining(@Param("nameOrCode") String nameOrCode);

    @Query(value = " SELECT * FROM wwyt_base_user WHERE REAL_NAME =?1 and code =?2 and DELETE_FLAG ='0' AND (state IS NULL OR state !=44) limit 1", nativeQuery = true)
    BaseUser findByRealNameAndCode(String userName, String userCode);

    @Query(value = " SELECT * FROM wwyt_base_user WHERE sn =?1 and DELETE_FLAG ='0' AND (state IS NULL OR state !=44)", nativeQuery = true)
    List<BaseUser> findBySn(String sn);

    @Query(value = "SELECT * FROM wwyt_base_user WHERE sn=?1 AND id!=?2 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44)", nativeQuery = true)
    List<BaseUser> findBySnAndIdNotEq(String sn, Long id);

    @Query(value = " SELECT * FROM wwyt_base_user WHERE card_number =?1 AND id !=?2 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44)", nativeQuery = true)
    List<BaseUser> findByCardNumberAndIdNotEq(String cardNumber, Long id);

    @Query(value = " SELECT Id FROM wwyt_base_user WHERE JOB = :job AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<Long> findIdByJobAndCompany(Long job);

    @Query(value = " SELECT Id FROM wwyt_base_user WHERE DEPART_ID = :departId  AND TENENT_ID = :tenentId AND ORG_CODE = :orgCode AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<Long> findBydepartIdAndCompany(Long departId, Long tenentId, String orgCode);

    @Query(value = "SELECT u.* FROM wwyt_base_user u INNER JOIN wwyt_base_image_repository r ON u.cap_id = r.id WHERE u.sn IS NOT NULL AND u.unique_id IS NOT NULL AND u.cap_id IS NOT NULL AND u.locate_sync = 1 AND u.delete_flag ='0' AND (u.state IS NULL OR u.state !=44) AND r.unique_id IS NOT NULL AND r.locate_sync = 1", nativeQuery = true)
    List<BaseUser> findBySnAndLocateSync();

    @Query(value = " SELECT Id FROM wwyt_base_user WHERE DEPART_ID = :departId AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<Long> findByDepartIdAndCompany(Long departId);

    @Transactional
    @Modifying
    @Query(value = "update wwyt_base_user set state = ?2 where id in ?1", nativeQuery = true)
    void setState(List<Long> ids, Integer state);

    @Transactional
    @Modifying
    @Query(value = "update wwyt_base_user set locate_sync = ?2 where id in ?1", nativeQuery = true)
    void editLocateSyncByIds(List<Long> ids, Integer i);

    @Query(value = " SELECT COUNT(*) FROM wwyt_base_user WHERE cap_id = ?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44)", nativeQuery = true)
    int countByCapId(Long capId);

    @Query(value = " SELECT * FROM wwyt_base_user WHERE unique_id = ?1  AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) limit 1", nativeQuery = true)
    BaseUser findByUniqueId(String uniqueId);

    @Query(value = " SELECT * FROM wwyt_base_user WHERE id = ?1  AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44)", nativeQuery = true)
    BaseUser findOneById(Long id);

    @Query(value = " SELECT * FROM wwyt_base_user WHERE code = ?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) AND ORG_CODE = ?2 AND TENENT_ID = ?3 ", nativeQuery = true)
    BaseUser findByCodeAndTenentIdAndOrgcode(String code, String orgCode, Long tenentId);

    @Query(value = "select REAL_NAME from `wwyt_base_user` where delete_flag = '0' and  (state is null or state !=44) group by REAL_NAME having count(*)>1", nativeQuery = true)
    List<String> getDuplicateUser();

    @Query(value = "select * from wwyt_base_user where id in ?1", nativeQuery = true)
    List<BaseUser> getAllByIds(List<Long> ids);

    @Query(value = "select * from wwyt_base_user where id in ?1", nativeQuery = true)
    List<BaseUser> getAllByIds(Set<Long> ids);

    @Query(value = "select * from wwyt_base_user where id in ?1", nativeQuery = true)
    List<BaseUser> getAllByIdsWithOutStatus(Set<Long> ids);

    @Query(value = "select distinct d.CHIEF_ID " +
            "from wwyt_base_user u " +
            "         left join wwyt_bp_depart d on u.DEPART_ID = d.id " +
            "where u.id in ?1 and d.CHIEF_ID is not null", nativeQuery = true)
    Set<Long> getDepartResponsibleByUserIds(List<Long> userIds);

    @Query(value = "select id from wwyt_base_user where job = ?1 and delete_flag = '0' and  (state is null or state !=44)", nativeQuery = true)
    List<Long> findAllUserByJob(Long jobId);

    @Query(value = "select count(1) from wwyt_base_user where delete_flag = '0' and  (state is null or state !=44)", nativeQuery = true)
    Integer getEffectiveUserCount();

    @Query(value = "select distinct(id) from wwyt_base_user where DELETE_FLAG = '0' and (state is null or state!=44) and DEPART_ID in ?1", nativeQuery = true)
    List<Long> getUserIdsByDepartIds(List<Long> departIds);

    @Query(value = "SELECT * FROM wwyt_base_user WHERE DEPART_ID IN (:parIds) AND if( :userName IS NOT NULL,real_name LIKE %:userName%,1=1) AND DELETE_FLAG = '0' AND (state IS NULL OR state!=44)", nativeQuery = true)
    List<BaseUser> getUsersByDepartIds(@Param("parIds") List<Long> parIds, @Param("userName") String userName);

    @Query(value = "SELECT * FROM wwyt_base_user WHERE id IN (:parIds) AND if( :departId IS NOT NULL,DEPART_ID =:departId,1=1) AND if( :userName IS NOT NULL,real_name LIKE %:userName%,1=1) AND DELETE_FLAG = '0' AND (state IS NULL OR state!=44)", nativeQuery = true)
    List<BaseUser> getUsersByIdsAndDepartIdAndUserName(@Param("parIds") List<Long> parIds, @Param("departId") Long departId, @Param("userName") String userName);

    @Query(value = "select distinct(id) from wwyt_base_user where DELETE_FLAG = '0' and (state is null or state!=44) and JOB in ?1", nativeQuery = true)
    List<Long> getUserIdsByJobIds(List<Long> jobIds);

    @Query(value = "SELECT * FROM wwyt_base_user WHERE JOB IN (:parIds) AND if( :departId IS NOT NULL,DEPART_ID =:departId,1=1) AND if( :userName IS NOT NULL,real_name LIKE %:userName%,1=1) AND DELETE_FLAG = '0' AND (state IS NULL OR state!=44)", nativeQuery = true)
    List<BaseUser> getUsersByJobIdsAndDepartIdAndUserName(@Param("parIds") List<Long> parIds, @Param("departId") Long departId, @Param("userName") String userName);

    @Query(value = "SELECT * FROM wwyt_base_user WHERE id IN (:userIdList)", nativeQuery = true)
    List<BaseUser> getUsersByIds(@Param("userIdList") List<Long> userIdList);

    @Query(value = "select * from wwyt_base_user where id in ?1 and (state is null or state!=44)", nativeQuery = true)
    List<BaseUser> getAllByIdsAndShowState(List<Long> parIds);

    @Query(value = "select REAL_NAME from wwyt_base_user where id in ?1 and (state is null or state!=44) and DELETE_FLAG = '0' ", nativeQuery = true)
    List<String> findNamesByIds(List<Long> userIds);

    @Query(value = "select id from wwyt_base_user where id in ?1 and (state is null or state!=44) and DELETE_FLAG = '0'", nativeQuery = true)
    List<Long> filterDeleteUser(List<Long> userIds);

    @Query(value = "select * from wwyt_base_user where id in ?1 and (state is null or state!=44) and DELETE_FLAG = '0'", nativeQuery = true)
    List<Long> filterDeleteUser(Set<Long> userIds);

    @Query(value = " select id from `wwyt_base_user` where REAL_NAME like %:resUserName% and (state is null or state!=44) and DELETE_FLAG = '0'", nativeQuery = true)
    List<Long> findIdsByRealName(@Param("resUserName") String resUserName);

    @Query(value = "select * from wwyt_base_user where depart_id in ?1 and job in ?2 and (state is null or state!=44) and DELETE_FLAG = '0'", nativeQuery = true)
    List<BaseUser> findByDepartIdsAndJobIds(List<Long> departIds, List<Long> jobIds);

    @Query(value = "select * from wwyt_base_user where code in :codes AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    List<BaseUser> findByCodes(List<String> codes);

    @Query(value = "select job from wwyt_base_user where id in ?1", nativeQuery = true)
    List<Long> findJobByIds(List<Long> userIds);

    @Query(value = " SELECT count(*) FROM wwyt_base_user WHERE real_name =?1 and DELETE_FLAG ='0' AND (state IS NULL OR state !=44) ", nativeQuery = true)
    Integer findByUserName(String userName);

    @Query(value = " SELECT * FROM wwyt_base_user WHERE DELETE_FLAG ='0' AND (state IS NULL OR state !=44) " +
            "AND if(:#{#params.userName} is null,1=1,REAL_NAME like %:#{#params.userName}%)" +
            "AND if(:#{#params.jobId} is null,1=1,JOB = :#{#params.jobId})" +
            "AND if(:#{#params.departId} is null,1=1,DEPART_ID = :#{#params.departId})"
            , nativeQuery = true)
    List<BaseUser> selectList(@Param("params") EduMarkingPapersCenterListBean.SearchEduMarkingPapersCenter params);


    @Query(value = "select autograph from wwyt_base_user where id = ?1 and (state is null or state!=44)", nativeQuery = true)
    String getAutograph(Long id);


    @Query(value = "select * from wwyt_base_user where face_token = ?1 and (state is null or state!=44)", nativeQuery = true)
    BaseUser getAutographByFaceToken(String faceToken);


    @Query(value = "select * from wwyt_base_user where id = ?1 and (state is null or state!=44) and  DELETE_FLAG ='0' ", nativeQuery = true)
    BaseUser getUserByUserId(Long id);

    @Query(value = "select * from wwyt_base_user where job = ?1 and DELETE_FLAG ='0'", nativeQuery = true)
    List<BaseUser> getJpbId(Long id);

    @Query(value = "select * from wwyt_base_user where depart_id = ?1 and DELETE_FLAG ='0'", nativeQuery = true)
    List<BaseUser> getPartId(Long id);
}
